/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package wads.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import wads.com.UserInfo;
import wads.com.UserInfoDataStruct;
import wads.com.WADTools;

/**
 *
 * @author Nattasan W.
 */
public class UsersLoader {
    String framework = "derbyclient";
    String driver = "com.mysql.jdbc.Driver";
//    String driver = "org.apache.derby.jdbc.EmbeddedDriver";
    String protocol = "jdbc:mysql://";
    String server = "db4free.net:3306/";
    static char[] pw = {'9', '9', '9'};

    public UsersLoader() {
        putData();
        //loadData();
    }

    private void loadDriver() {
        try {
            Class.forName(driver).newInstance();
            System.out.println("Loaded the appropriate driver");
        }
        catch (ClassNotFoundException cnfe) {
            System.err.println("\nUnable to load the JDBC driver " + driver);
            System.err.println("Please check your CLASSPATH.");
            cnfe.printStackTrace(System.err);
        }
        catch (InstantiationException ie) {
            System.err.println("\nUnable to instantiate the JDBC driver " + driver);
            ie.printStackTrace(System.err);
        }
        catch (IllegalAccessException iae) {
            System.err.println("\nNot allowed to access the JDBC driver " + driver);
            iae.printStackTrace(System.err);
        }
    }

    public UserInfo loadData() {
        String user = "admin";
        String password = "admin";
        String schema = "app.";
        String database = "databases/WadsDB";
        UserInfo members = new UserInfo();
        UserInfoDataStruct member;

        try {
            loadDriver();
            Connection con = DriverManager.getConnection(protocol+server+database, user, password);
            Statement stmt = con.createStatement();
            String query = "select * from "+schema+"users";
            ResultSet rs = stmt.executeQuery(query);

            while(rs.next()) {
                member = new UserInfoDataStruct();
                System.out.println(rs.getInt("uid"));
                member.setId(rs.getInt("uid"));
                System.out.println(rs.getString("username"));
                member.setUsername(rs.getString("username"));
                System.out.println(rs.getBytes("password"));
                member.setEncryptedPassword(rs.getBytes("password"));
                System.out.println(rs.getString("name"));
                member.setName(rs.getString("name"));
                System.out.println(rs.getString("position"));
                member.setPosition(rs.getString("position"));
                System.out.println(rs.getString("email"));
                member.setEMail(rs.getString("email"));
                System.out.println(rs.getString("roles")+"\n");
                member.setRoles(rs.getString("roles"));
            }

            con.close();
            stmt.close();

            return members;
        }
        catch (SQLException ex) {
            Logger.getLogger(UsersLoader.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
        catch (Exception ex) {
            Logger.getLogger(UsersLoader.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public void putData() {
        String user = "xenogew";
        String password = "widwacom";
        String schema = "";
        String database = "wads";

        try {
            loadDriver();
            Connection con = DriverManager.getConnection(protocol+server+database, user, password);
            String query = "insert into "+schema+"users (username,password,name,position,email,roles) values (?,?,?,?,?,?)";
            PreparedStatement pstmt = con.prepareStatement(query);

            pstmt.setString(1, "xenogew1");
            pstmt.setBytes(2, WADTools.encryptPassword(pw));
            pstmt.setString(3, "ณัฐสัญญ์ วังอภิสิทธิ์");
            pstmt.setString(4, "student");
            pstmt.setString(5, "xenogew@gmail.com");
            pstmt.setString(6, "admin");

            pstmt.execute();

            con.close();
            pstmt.close();
        }

        catch (SQLException ex) {
            System.err.println("Error.");
            Logger.getLogger(UsersLoader.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch (Exception ex) {
            Logger.getLogger(UsersLoader.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void main(String[] args) {
        new UsersLoader();
    }

    public UserInfo getUsers() {
        return null;
    }

}
